﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using TallyBook.DesignLib.Attributes;

namespace TallyBook.DesignLib
{
    /// <summary>
    ///     构造sql语句
    /// </summary>
    public class SqlBuilder
    {
        #region 删除

        public static string DeleteComplex<TEntity>(TEntity entity)
        {
            string sqlStr = "delete from [" + typeof (TEntity).Name + "] where";

            //找出主键
            List<PropertyInfo> primaryKeys = typeof (TEntity)
                .GetProperties()
                .Where(property =>
                {
                    object[] attrs = property.GetCustomAttributes(typeof (PrimaryKeyAttribute), false);
                    if (attrs.Length == 0)
                        return false;
                    var primaryKey = attrs[0] as PrimaryKeyAttribute;
                    if (primaryKey == null)
                        return false;
                    return true;
                }).ToList();
            if (primaryKeys.Count == 0)
                return sqlStr;

            //根据主键查询
            primaryKeys.ForEach(item => { sqlStr += "[" + item.Name + "]='" + item.GetValue(entity) + "'" + "and "; });
            sqlStr = sqlStr.Remove(sqlStr.Length - 4, 4);
            return sqlStr;
        }

        #endregion

        #region 更新

        public static string UpdateComplex<TEntity>(TEntity entity)
        {
            string sqlStr = "update[" + typeof (TEntity).Name + "]set";

            //取出普通属性
            List<PropertyInfo> normalPropertie = typeof (TEntity).GetProperties()
                .Where(property =>
                {
                    object[] attrs = property.GetCustomAttributes(typeof (PrimaryKeyAttribute), false);
                    if (attrs.Length == 0)
                        return true;
                    var primaryKey = attrs[0] as PrimaryKeyAttribute;
                    if (primaryKey == null)
                        return true;
                    return false;
                }).ToList();
            //生成where前面的部分   
            normalPropertie.ForEach(property =>
            {
                object value = property.GetValue(entity);
                if (property.PropertyType == typeof (DateTime))
                {
                    sqlStr += "[" + property.Name + "]=";
                    sqlStr += "'" + ((DateTime) value).ToString("s") + "',";
                }
                else if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                {
                    sqlStr += "[" + property.Name + "]=";
                    sqlStr += "'" + value + "',";
                }
            });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            //找出主键
            List<PropertyInfo> primaryKeys = typeof (TEntity)
                .GetProperties()
                .Where(property => !normalPropertie.Contains(property)).ToList();
            if (primaryKeys.Count == 0)
                return sqlStr;


            //根据主键查询
            sqlStr += "where ";
            primaryKeys.ForEach(item => { sqlStr += "[" + item.Name + "]='" + item.GetValue(entity) + "'" + "and "; });
            sqlStr = sqlStr.Remove(sqlStr.Length - 4, 4);
            return sqlStr;
        }

        #endregion

        #region 链表复杂查询

        private static string GetPrimaryKey(Type entity)
        {
            return entity.GetProperties()
                .Select(item =>
                {
                    object[] attrs = item.GetCustomAttributes(typeof (PrimaryKeyAttribute), false);
                    if (attrs.Length == 0)
                        return null;
                    return item.Name;
                }).FirstOrDefault(item => item != null);
        }

        private static string GetForeignKey(Type entity, Type foreignType = null)
        {
            return entity.GetProperties()
                .Select(item =>
                {
                    object[] attrs = item.GetCustomAttributes(typeof (ForeignerKeyAttribute), false);
                    if (attrs.Length == 0)
                        return null;
                    if (foreignType == null || item.PropertyType == foreignType)
                        return ((ForeignerKeyAttribute) attrs[0]).Name;
                    return null;
                }).FirstOrDefault(item => item != null);
        }


        private static string SelectFied(PropertyInfo property)
        {
            return string.Format("{0}.{1} as  {0}_{1}", property.DeclaringType.Name, property.Name);
        }

        /// <summary>
        ///     根据主键查询
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string GetComplex(Type entity, long id)
        {
            string sqlStr = "select ";

            string tableStr = " from " + entity.Name;

            entity.GetProperties()
                .ToList()
                .ForEach(property =>
                {
                    if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                        sqlStr += SelectFied(property) + ",";
                    else
                    {
                        property.PropertyType.GetProperties()
                            .ToList().ForEach(item => { sqlStr += SelectFied(item) + ","; });

                        tableStr += " inner join " + property.PropertyType.Name + " on " +
                                    entity.Name + "." + GetForeignKey(entity, property.PropertyType) + "="
                                    + property.PropertyType.Name + "." + GetPrimaryKey(property.PropertyType);
                    }
                });

            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += tableStr;

            sqlStr += " where " + entity.Name + "." + GetPrimaryKey(entity) + "=" + id;

            return sqlStr;
        }

        /// <summary>
        ///     查询全部
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <returns></returns>
        public static string GetAllComplex<TEntity>()
        {
            string sqlStr = "select ";

            string tableStr = " from " + typeof (TEntity).Name;

            typeof (TEntity).GetProperties()
                .ToList()
                .ForEach(property =>
                {
                    if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                        sqlStr += SelectFied(property) + ",";
                    else
                    {
                        property.PropertyType.GetProperties()
                            .ToList().ForEach(item => { sqlStr += SelectFied(item) + ","; });

                        tableStr += " inner join " + property.PropertyType.Name + " on " +
                                    typeof (TEntity).Name + "." + GetForeignKey(typeof (TEntity), property.PropertyType) +
                                    "="
                                    + property.PropertyType.Name + "." + GetForeignKey(property.PropertyType);
                    }
                });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += tableStr;

            return sqlStr;
        }

        /// <summary>
        ///     模糊查询
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static string GetDimComplex<TEntity>(long entity)
        {
            string sqlStr = "select ";

            string tableStr = " from " + typeof (TEntity).Name;

            typeof (TEntity).GetProperties()
                .ToList()
                .ForEach(property =>
                {
                    if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                        sqlStr += SelectFied(property) + ",";
                    else
                    {
                        property.PropertyType.GetProperties()
                            .ToList().ForEach(item => { sqlStr += SelectFied(item) + ","; });

                        tableStr += " inner join " + property.PropertyType.Name + " on " +
                                    typeof (TEntity).Name + "." + GetForeignKey(typeof (TEntity), property.PropertyType) +
                                    "="
                                    + property.PropertyType.Name + "." + GetForeignKey(property.PropertyType);
                    }
                });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += tableStr;

            sqlStr += " where " + typeof (TEntity).Name + "." + GetForeignKey(typeof (TEntity))
                      + " like '%" + entity + "%'";

            return sqlStr;
        }

        public static string GetDimComplex<TEntity>(decimal entity)
        {
            string sqlStr = "select ";

            string tableStr = " from " + typeof (TEntity).Name;

            typeof (TEntity).GetProperties()
                .ToList()
                .ForEach(property =>
                {
                    if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                        sqlStr += SelectFied(property) + ",";
                    else
                    {
                        property.PropertyType.GetProperties()
                            .ToList().ForEach(item => { sqlStr += SelectFied(item) + ","; });

                        tableStr += " inner join " + property.PropertyType.Name + " on " +
                                    typeof (TEntity).Name + "." + GetForeignKey(typeof (TEntity), property.PropertyType) +
                                    "="
                                    + property.PropertyType.Name + "." + GetForeignKey(property.PropertyType);
                    }
                });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += tableStr;

            sqlStr += " where " + typeof (TEntity).Name + "." + "Money" + " like '%" + entity + "%'";


            return sqlStr;
        }

        #endregion

        #region 增加

        public static string InsertComplex<TEntity>(TEntity entity)
        {
            string sqlStr = "insert into [" + typeof (TEntity).Name + "] (";

            typeof (TEntity).GetProperties()
                .ToList()
                .ForEach(property =>
                {
                    if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                        sqlStr += "[" + property.Name + "],";
                });
            sqlStr = sqlStr.Remove(sqlStr.Length - 1);

            sqlStr += ") values (";

            typeof (TEntity).GetProperties()
                .ToList()
                .ForEach(property =>
                {
                    object value = property.GetValue(entity);
                    if (property.PropertyType == typeof (DateTime))
                        sqlStr += "'" + ((DateTime) value).ToString("s") + "',";

                    else if (property.PropertyType.IsValueType || property.PropertyType == typeof (string))
                        sqlStr += "'" + value + "',";
                });

            sqlStr = sqlStr.Remove(sqlStr.Length - 1);
            sqlStr += ")";

            return sqlStr;
        }

        #endregion
    }
}